package utils;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class DatabaseUtils {

    private static final Properties DB_PROPS = new Properties();

    // 加载数据库配置文件
    static {
        try (InputStream input = DatabaseUtils.class.getClassLoader().getResourceAsStream("config/database.properties")) {
            if (input == null) {
                throw new IOException("配置文件未找到");
            }
            DB_PROPS.load(input);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    // 获取数据库连接
    public static Connection getConnection() throws ClassNotFoundException, SQLException {
        String driver = DB_PROPS.getProperty("driver");
        String url = DB_PROPS.getProperty("url");
        String user = DB_PROPS.getProperty("user");
        String password = DB_PROPS.getProperty("password");

        Class.forName(driver);
        return DriverManager.getConnection(url, user, password);
    }

    // 执行查询语句
    public static JSONObject executeQuery(String sql) throws ClassNotFoundException, SQLException {
        JSONObject json = new JSONObject();
        try (Connection conn = getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            while (rs.next()) {
                json.put("email", rs.getString("email"));
                json.put("password", rs.getString("password"));
            }
        }
        return json;
    }

    public static JSONObject executeQueryForArray(String sql) throws ClassNotFoundException, SQLException {
        JSONArray results = new JSONArray();
        try (Connection conn = getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            while (rs.next()) {
                JSONObject row = new JSONObject();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnName(i);
                    Object columnValue = rs.getObject(i);
                    row.put(columnName, columnValue);
                }
                results.add(row); // 使用add方法而不是put
            }
        }

        // 创建一个JSONObject并添加results键值对
        JSONObject resultJson = new JSONObject();
        resultJson.put("results", results);
        return resultJson;
    }

    // 执行更新语句
    public static JSONObject executeUpdate(String sql) throws ClassNotFoundException, SQLException {
        JSONObject json = new JSONObject();
        try (Connection conn = getConnection();
             Statement stmt = conn.createStatement()) {
            int updateCount = stmt.executeUpdate(sql);
            if (updateCount > 0) {
                json.put("status", "success");
                json.put("message", "操作成功");
            } else {
                json.put("status", "error");
                json.put("message", "操作失败");
            }
        }
        return json;
    }
}
